﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Text.RegularExpressions;
using CNVP.Config;

namespace CNVP.Framework.Helper
{
    public sealed class DbHelper
    {
        public static string connectionString;
        static DbHelper()
        {
            connectionString = Config.DBConfig.DBConn; //加密连接字符串

        }
        #region Initial
        /// <summary>
        /// 对命令的属性（如连接、事务环境等）进行初始化。
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="conn"></param>
        /// <param name="trans"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            command.Connection = connection;
            command.CommandText = commandText;
            if (transaction != null)
            {
                if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
                command.Transaction = transaction;
            }
            command.CommandType = commandType;
            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }
            return;
        }
        /// <summary>
        /// 将所有必要的SqlParameter对象连接到正在运行的SqlCommand。
        /// </summary>
        /// <param name="command"></param>
        /// <param name="commandParameters"></param>
        private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (commandParameters != null)
            {
                foreach (SqlParameter p in commandParameters)
                {
                    if (p != null)
                    {
                        if ((p.Direction == ParameterDirection.InputOutput ||
                            p.Direction == ParameterDirection.Input) &&
                            (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }
                        command.Parameters.Add(p);
                    }
                }
            }
        }
        #endregion
        #region ExecuteNonQuery
        /// <summary>
        /// 执行SQL语句，返回一个整型变量。
        /// <para>如果对数据库进行操作，返回受影响的记录条数；</para>
        /// <para>如果进行创建表的操作，创建成功后返回值为-1。</para>
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql)
        {
            int count = -1;
            if (String.IsNullOrEmpty(sql))
                return count;

            return ExecuteNonQuery(sql, (SqlParameter[])null);
        }
        /// <summary>
        /// 执行SQL语句，返回一个整型变量。
        /// <para>如果对数据库进行操作，返回受影响的记录条数；</para>
        /// <para>如果进行创建表的操作，创建成功后返回值为-1。</para>
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, SqlParameter[] parameters)
        {
            CommandType commandType = CommandType.Text;
            return ExecuteNonQuery(commandType, sql, parameters);
        }
        /// <summary>
        /// 执行SQL语句，返回一个整型变量。
        /// <para>如果对数据库进行操作，返回受影响的记录条数；</para>
        /// <para>如果进行创建表的操作，创建成功后返回值为-1。</para>
        /// </summary>
        /// <param name="commandType">存储过程、表名、Sql语句(默认)</param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(CommandType commandType, string commandText, SqlParameter[] parameters)
        {
            int count = -1;
            if (string.IsNullOrEmpty(commandText) && parameters == null)
                return count;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {

                    try
                    {
                        PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, parameters);
                        count = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return count;

                    }
                    catch
                    {
                        connection.Close();
                        count = -1;
                        throw;
                    }

                }
            }
        }
        #endregion
        #region ExecuteScalar
        /// <summary>
        /// 执行SQL语句，返回数值。
        /// <para>如果执行查询操作，返回第一行第一列；</para>
        /// <para>如果不是查询操作，无返回值。</para>
        /// </summary>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string commandText)
        {
            CommandType commandType = CommandType.Text;
            object ec = ExecuteScalar(commandType, commandText);
            return ec;
        }
        /// <summary>
        /// 执行SQL语句，返回数值。
        /// <para>如果执行查询操作，返回第一行第一列；</para>
        /// <para>如果不是查询操作，无返回值。</para>
        /// </summary>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string commandText, params SqlParameter[] commandParameters)
        {
            CommandType commandType = CommandType.Text;
            object ec = ExecuteScalar(commandType, commandText, commandParameters);
            return ec;
        }
        /// <summary>
        /// 执行SQL语句，返回数值。
        /// <para>如果执行查询操作，返回第一行第一列；</para>
        /// <para>如果不是查询操作，无返回值。</para>
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public static object ExecuteScalar(CommandType commandType, string commandText)
        {
            return ExecuteScalar(commandType, commandText, (SqlParameter[])null);
        }
        /// <summary>
        /// 执行SQL语句，返回数值。
        /// <para>如果执行查询操作，返回第一行第一列；</para>
        /// <para>如果不是查询操作，无返回值。</para>
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object ExecuteScalar(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
                        object retval = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        return retval;
                    }
                    catch
                    {
                        connection.Close();
                        throw;
                    }
                }
            }
        }
        #endregion
        #region ExecuteReader
        /// <summary>
        /// 执行查询语句，返回SqlDataReader对象。
        /// <para>注意：需要手动关闭SqlDataReader和数据库链接。</para>
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string strSQL)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(strSQL, connection);
            try
            {
                connection.Open();
                SqlDataReader myReader = cmd.ExecuteReader();
                return myReader;               
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
        }
        /// <summary>
        /// 执行查询语句，返回SqlDataReader对象。
        /// <para>注意：需要手动关闭SqlDataReader和数据库链接。</para>
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SqlDataReader</returns>     
        public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, connection, null,CommandType.Text, SQLString, cmdParms);
                SqlDataReader myReader = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                return myReader;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }           
        }
        #endregion
        #region ExecuteDataSet
        /// <summary>
        /// 执行Sql语句，返回记录集。
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(string sql)
        {
            if (String.IsNullOrEmpty(sql))
                return null;
            return ExecuteDataSet(sql, (SqlParameter[])null);
        }
        /// <summary>
        /// 执行Sql语句，返回记录集。
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(string sql, params SqlParameter[] parameters)
        {
            if (string.IsNullOrEmpty(sql) && parameters == null)
                return null;
            return ExecuteDataSet(CommandType.Text, sql, parameters);
        }
        /// <summary>
        /// 执行Sql语句，返回记录集。
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
                        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                        {
                            DataSet ds = new DataSet();
                            da.Fill(ds);
                            cmd.Parameters.Clear();
                            return ds;
                        }
                    }
                    catch
                    {
                        connection.Close();
                        throw;
                    }
                }
            }
        }
        #endregion
        #region ExecuteDateTable
        /// <summary>
        /// 执行Sql语句，返回记录集。
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable ExecuteTable(string sql)
        {
            if (String.IsNullOrEmpty(sql))
                return null;
            return ExecuteTable(sql, (SqlParameter[])null);
        }
        /// <summary>
        /// 执行Sql语句，返回记录集。
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static DataTable ExecuteTable(string cmdText, params  SqlParameter[] commandParameters)
        {
            return ExecuteTable(CommandType.Text, cmdText, commandParameters);
        }
        /// <summary>
        /// 执行Sql语句，返回记录集。
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static DataTable ExecuteTable(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                    SqlDataAdapter ap = new SqlDataAdapter();
                    ap.SelectCommand = cmd;
                    DataSet st = new DataSet();
                    ap.Fill(st, "Result");
                    cmd.Parameters.Clear();
                    return st.Tables["Result"];
                }
                catch
                {
                    throw;
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                    }
                    connection.Dispose();
                }
            }
        }
        #endregion
        #region ExecuteSP
        /// <summary>
        /// 执行存储过程，返回受影响行数。
        /// </summary>
        /// <param name="procName"></param>
        /// <returns></returns>
        public static int ExecuteSP(string procName)
        {
            return ExecuteNonQuery(CommandType.StoredProcedure, procName, (SqlParameter[])null);
        }
        /// <summary>
        /// 执行存储过程，返回记录集。
        /// </summary>
        /// <param name="procName"></param>
        /// <returns></returns>
        public static void ExecuteSP(string procName, out DataSet ds)
        {
            ds = ExecuteDataSet(CommandType.StoredProcedure, procName, (SqlParameter[])null);
        }
        /// <summary>
        /// 执行存储过程，返回结果集第一行第一列的数据。
        /// <para>例如：System.Datetime dt = (System.Datetime)RunProc("GetDate");</para>
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <returns></returns>
        public static void ExecuteSP(string procName, out object obj)
        {
            obj = ExecuteScalar(CommandType.StoredProcedure, procName, (SqlParameter[])null);
        }
        /// <summary>
        /// 执行存储过程，返回结果集第一行第一列的数据。
        ///<para>例如：System.Datetime dt = (System.Datetime)RunProc("GetDate");</para>
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="prams"></param>
        /// <returns></returns>
        public static void ExecuteSP(string procName, SqlParameter[] prams, out object obj)
        {
            obj = ExecuteScalar(CommandType.StoredProcedure, procName, prams);
        }
        /// <summary>
        /// 执行存储过程，返回受影响行数。
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="prams"></param>
        /// <returns></returns>
        public static int ExecuteSP(string procName, SqlParameter[] prams)
        {
            return ExecuteNonQuery(CommandType.StoredProcedure, procName, prams);
        }
        /// <summary>
        /// 执行存储过程，返回记录集。
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="prams"></param>
        /// <returns></returns>
        public static void ExecuteSP(string procName, SqlParameter[] prams, out DataSet ds)
        {
            ds = ExecuteDataSet(CommandType.StoredProcedure, procName, prams);
        }
        /// <summary>
        /// 执行存储过程，返回记录集。
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        public static DataSet ExecuteSP(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }
        #region 构建SqlCommand对象
        /// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand</returns>
        private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    // 检查未分配值的输出参数,将其分配以DBNull.Value.
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }
            return command;
        }
        #endregion
        #endregion
        #region ExecuteCommandWithSplitter方法
        /// <summary>
        /// 运行含有GO命令的多条SQL命令
        /// </summary>
        /// <param name="commandText">SQL命令字符串</param>
        /// <param name="splitter">分割字符串</param>
        public static void ExecuteCommandWithSplitter(string commandText, string splitter)
        {
            int startPos = 0;

            do
            {
                int lastPos = commandText.IndexOf(splitter, startPos);
                int len = (lastPos > startPos ? lastPos : commandText.Length) - startPos;
                string query = commandText.Substring(startPos, len);

                if (query.Trim().Length > 0)
                {
                    try
                    {
                        ExecuteNonQuery(query);
                    }
                    catch { ;}
                }

                if (lastPos == -1)
                    break;
                else
                    startPos = lastPos + splitter.Length;
            } while (startPos < commandText.Length);

        }

        /// <summary>
        /// 运行含有GO命令的多条SQL命令
        /// </summary>
        /// <param name="commandText">SQL命令字符串</param>
        public static void ExecuteCommandWithSplitter(string commandText)
        {
            ExecuteCommandWithSplitter(commandText, "\r\nGO\r\n");
        }
        #endregion ExecuteCommandWithSplitter方法结束
        #region Make SqlParameters
        /// <summary>
        /// 输入参数
        /// </summary>
        /// <param name="ParamName">参数名称</param>
        /// <param name="DbType">参数类型</param>
        /// <param name="Size">参数大小</param>
        /// <param name="Value">参数值</param>
        /// <returns></returns>
        public static SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
        {
            return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
        }
        /// <summary>
        /// 输出参数
        /// </summary>
        /// <param name="ParamName">参数名称</param>
        /// <param name="DbType">参数类型</param>
        /// <param name="Size">参数大小</param>
        /// <returns></returns>
        public static SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
        {
            return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
        }
        /// <summary>
        /// 函数参数(支持输入输出)
        /// </summary>
        /// <param name="ParamName">参数名称</param>
        /// <param name="DbType">参数类型</param>
        /// <param name="Size">参数大小</param>
        /// <param name="Direction">输入或输入</param>
        /// <param name="Value">参数值</param>
        /// <returns></returns>
        public static SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
        {
            SqlParameter param;
            if (Size > 0)
                param = new SqlParameter(ParamName, DbType, Size);
            else
                param = new SqlParameter(ParamName, DbType);
            param.Direction = Direction;
            if (!(Direction == ParameterDirection.Output && Value == null))
                param.Value = Value;
            return param;
        }
        #endregion
        #region Page
        /// <summary>
        /// 分页函数
        /// </summary>
        /// <param name="SqlAllFields">查询字段，如果是多表查询，请将必要的表名或别名加上，如:a.id,b.username</param>
        /// <param name="SqlTablesAndWhere">查询的表如果包含查询条件，也将条件带上，但不要包含order by子句，也不要包含"from"关键字，如:students a inner join achievement b on a.... where ....</param>
        /// <param name="IndexField">用以分页的不能重复的索引字段名，最好是主表的自增长字段，如果是多表查询，请带上表名或别名，如:a.id</param>
        /// <param name="OrderFields">排序字段以及方式如：a.OrderID desc,CnName desc</param>
        /// <param name="PageIndex">当前页的页码</param>
        /// <param name="PageSize">每页记录数</param>
        /// <param name="RecordCount">输出参数，返回查询的总记录条数</param>
        /// <param name="PageCount">输出参数，返回查询的总页数</param>
        /// <param name="commandParameters">执行查询语句的时候尽量使用带参数的模式</param>
        /// <returns></returns>
        public static DataTable ExecutePage(string SqlAllFields, string SqlTablesAndWhere, string IndexField, string OrderFields, int PageIndex, int PageSize, out int RecordCount, out int PageCount, params SqlParameter[] commandParameters)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                return ExecutePage(connection, SqlAllFields, SqlTablesAndWhere, IndexField, OrderFields, PageIndex, PageSize, out  RecordCount, out  PageCount, commandParameters);

            }
        }
        /// <summary>
        /// 分页函数
        /// </summary>
        /// <param name="connection">SQL数据库连接对象</param>
        /// <param name="SqlAllFields">查询字段，如果是多表查询，请将必要的表名或别名加上，如:a.id,a.name,b.score</param>
        /// <param name="SqlTablesAndWhere">查询的表如果包含查询条件，也将条件带上，但不要包含order by子句，也不要包含"from"关键字，如:students a inner join achievement b on a.... where ....</param>
        /// <param name="IndexField">用以分页的不能重复的索引字段名，最好是主表的自增长字段，如果是多表查询，请带上表名或别名，如:a.id</param>
        /// <param name="OrderASC">排序方式,如果为true则按升序排序,false则按降序排</param>
        /// <param name="OrderFields">排序字段以及方式如：a.OrderID desc,CnName desc</OrderFields>
        /// <param name="PageIndex">当前页的页码</param>
        /// <param name="PageSize">每页记录数</param>
        /// <param name="RecordCount">输出参数，返回查询的总记录条数</param>
        /// <param name="PageCount">输出参数，返回查询的总页数</param>
        /// <returns>返回查询结果</returns>
        private static DataTable ExecutePage(SqlConnection connection, string SqlAllFields, string SqlTablesAndWhere, string IndexField, string OrderFields, int PageIndex, int PageSize, out int RecordCount, out int PageCount, params  SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, connection, (SqlTransaction)null, CommandType.Text, SqlAllFields, commandParameters);
            string Sql = GetPageSql(cmd, SqlAllFields, SqlTablesAndWhere, IndexField, OrderFields, PageIndex, PageSize, out  RecordCount, out  PageCount);
            cmd.CommandText = Sql;
            SqlDataAdapter ap = new SqlDataAdapter();
            ap.SelectCommand = cmd;
            DataSet st = new DataSet();
            ap.Fill(st, "PageResult");
            cmd.Parameters.Clear();

            return st.Tables["PageResult"];
        }
        /// <summary>
        /// 分页函数
        /// </summary>
        /// <param name="SqlAllFields"></param>
        /// <param name="SqlTablesAndWhere"></param>
        /// <param name="IndexField"></param>
        /// <param name="GroupClause"></param>
        /// <param name="OrderFields"></param>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        /// <param name="RecordCount"></param>
        /// <param name="PageCount"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        private static SqlDataReader ExecuteReaderPage(string SqlAllFields, string SqlTablesAndWhere, string IndexField, string GroupClause, string OrderFields, int PageIndex, int PageSize, out int RecordCount, out int PageCount, params SqlParameter[] commandParameters)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, (SqlTransaction)null, CommandType.Text, SqlAllFields, commandParameters);
                        string Sql = GetPageSql(cmd, SqlAllFields, SqlTablesAndWhere, IndexField, OrderFields, PageIndex, PageSize, out  RecordCount, out  PageCount);
                        if (GroupClause != null && GroupClause.Trim() != "")
                        {
                            int n = Sql.ToLower().LastIndexOf(" order by ");
                            Sql = Sql.Substring(0, n) + " " + GroupClause + " " + Sql.Substring(n);
                        }
                        cmd.CommandText = Sql;
                        SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        cmd.Parameters.Clear();
                        return rdr;
                    }
                    catch
                    {
                        throw;
                    }
                    finally
                    {
                    }
                }
            }
        }
        /// <summary>
        /// 分页函数
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="cmd"></param>
        /// <param name="SqlAllFields"></param>
        /// <param name="SqlTablesAndWhere"></param>
        /// <param name="IndexField"></param>
        /// <param name="OrderFields"></param>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        /// <param name="RecordCount"></param>
        /// <param name="PageCount"></param>
        /// <returns></returns>
        private static string GetPageSql(SqlCommand cmd, string SqlAllFields, string SqlTablesAndWhere, string IndexField, string OrderFields, int PageIndex, int PageSize, out int RecordCount, out int PageCount)
        {
            RecordCount = 0;
            PageCount = 0;
            if (PageSize <= 0)
            {
                PageSize = 10;
            }
            string SqlCount = "select count(" + IndexField + ") from " + SqlTablesAndWhere;
            cmd.CommandText = SqlCount;
            RecordCount = (int)cmd.ExecuteScalar();
            if (RecordCount % PageSize == 0)
            {
                PageCount = RecordCount / PageSize;
            }
            else
            {
                PageCount = RecordCount / PageSize + 1;
            }
            if (PageIndex > PageCount)
                PageIndex = PageCount;
            if (PageIndex < 1)
                PageIndex = 1;
            string Sql = null;
            if (PageIndex == 1)
            {
                Sql = "select top " + PageSize + " " + SqlAllFields + " from " + SqlTablesAndWhere + " " + OrderFields;
            }
            else
            {
                Sql = "select top " + PageSize + " " + SqlAllFields + " from ";
                if (SqlTablesAndWhere.ToLower().IndexOf(" where ") > 0)
                {
                    string _where = Regex.Replace(SqlTablesAndWhere, @"\ where\ ", " where (", RegexOptions.IgnoreCase | RegexOptions.Compiled);
                    Sql += _where + ") and (";
                }
                else
                {
                    Sql += SqlTablesAndWhere + " where (";
                }
                Sql += IndexField + " not in (select top " + (PageIndex - 1) * PageSize + " " + IndexField + " from " + SqlTablesAndWhere + " " + OrderFields;
                Sql += ")) " + OrderFields;
            }
            return Sql;
        }
        #region "分页显示代码"
        /// <summary>
        /// 返回页面链接参数
        /// </summary>
        /// <returns></returns>
        private static string QueryUrl()
        {
            /*遍历Form与QueryString对象
            StringBuilder Url = new StringBuilder();
            foreach (string Key in HttpContext.Current.Request.QueryString.Keys)
            {
                Url.Append("&" + Key + "=" + HttpContext.Current.Request.QueryString[Key]);
            }
            foreach (string Key in HttpContext.Current.Request.Form.Keys)
            {
                if (Key != "__EVENTVALIDATION" & Key != "__VIEWSTATE")
                {
                    Url.Append("&" + Key + "=" + HttpContext.Current.Request.Form[Key]);
                }
            }
            string Str = Url.ToString();
            Str = _Regex.Replace(Str, string.Empty);
             */
            Regex _Regex = new Regex(@"^&PageNo=\d+", RegexOptions.Compiled);
            string Str = HttpContext.Current.Request.Url.Query.Replace("?", "&");
            Str = _Regex.Replace(Str, string.Empty);
            return Str;
        }
        /// <summary>
        /// 分页函数(最简单模式)
        /// </summary>
        /// <param name="RecordCount">记录总数</param>
        /// <param name="PageCount">页面总数</param>
        /// <param name="PageSize">每页记录数</param>
        /// <param name="PageNo">当前页面</param>
        /// <param name="Query">查询条件：例如：UserName=Apollo</param>
        /// <returns></returns>
        public static string GetPageNormal(int RecordCount, int PageCount, int PageSize, int PageNo)
        {
            string Str = ""; ;

            Str = "共" + RecordCount + "条记录 页次：" + PageNo + "/" + PageCount + "页 ";
            Str += PageSize + "条/页 ";

            if (Convert.ToInt32(PageNo) < 2)
                Str += "首页 上页 ";
            else
            {
                Str += "<a href=\"?PageNo=1" + QueryUrl() + "\">首页</a> ";
                Str += "<a href=\"?PageNo=" + (PageNo - 1) + QueryUrl() + "\">上页</a> "; ;
            }
            if (PageCount - Convert.ToInt32(PageNo) < 1)
            {
                Str += "下页 尾页 ";
            }
            else
            {
                Str += "<a href=\"?PageNo=" + (PageNo + 1) + QueryUrl() + "\">下页</a> ";
                Str += "<a href=\"?PageNo=" + PageCount + QueryUrl() + "\">尾页</a>  ";
            }
            return Str;
        }
        /// <summary>
        /// 分页函数(仿Google分页)
        /// </summary>
        /// <param name="RecordCount">记录总数</param>
        /// <param name="PageCount">分页总数</param>
        /// <param name="PageSize">每个条数</param>
        /// <param name="PageNo">当前页码</param>
        /// <returns></returns>
        public static string GetPageGoogle(int RecordCount,int PageCount,int PageSize,int PageNo)
        {
            int Next, Pre, StartCount, EndCount = 0;
            if (PageNo < 1){PageNo = 1;}
            Next = PageNo + 1;
            Pre = PageNo - 1;
            StartCount = (PageNo + 5) > PageCount ? PageCount - 9 : PageNo - 1;
            EndCount = PageNo < 5 ? 10 : PageNo + 5;
            if (StartCount < 1) { StartCount = 1; }
            if (PageCount < EndCount) { EndCount = PageCount; }
            string Str = "";
            Str += "共" + RecordCount + "条记录，共" + PageCount + "页&nbsp;";
            Str += PageNo > 1 ? "<a href=\"?PageNo=1" + QueryUrl() + "\">首页</a>&nbsp;<a href=\"?PageNo=" + Pre + QueryUrl() + "\">上一页</a>" : "首页 上一页";
            for (int i = StartCount; i <= EndCount; i++)
            {
                Str += PageNo == i ? "&nbsp;<font color=\"#ff0000\">" + i + "</font>" : "&nbsp;<a href=\"?PageNo=" + i + QueryUrl() + "\">" + i + "</a>";
            }
            Str += PageNo != PageCount ? "&nbsp;<a href=\"?PageNo=" + Next + QueryUrl() + "\">下一页</a>&nbsp;<a href=\"?PageNo=" + PageCount + QueryUrl() + "\">末页</a>" : " 下一页 末页";
            return Str;
        }
        /// <summary>
        /// 分页DataTable
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        /// <returns></returns>
        public static DataTable GetPagedTable(DataTable dt, int PageIndex, int PageSize)
        {

            if (PageIndex == 0)
                return dt;
            DataTable newdt = dt.Clone();
            int rowbegin = (PageIndex - 1) * PageSize;
            int rowend = PageIndex * PageSize;

            if (rowbegin >= dt.Rows.Count)
                return newdt;

            if (rowend > dt.Rows.Count)
                rowend = dt.Rows.Count;
            for (int i = rowbegin; i <= rowend - 1; i++)
            {
                DataRow newdr = newdt.NewRow();
                DataRow dr = dt.Rows[i];
                foreach (DataColumn column in dt.Columns)
                {
                    newdr[column.ColumnName] = dr[column.ColumnName];
                }
                newdt.Rows.Add(newdr);
            }

            return newdt;
        }
        #endregion
        #endregion

        #region 公用方法
        /// <summary>
        /// 获取某表，中某字段的最大值
        /// </summary>
        /// <param name="FieldName">字段</param>
        /// <param name="TableName">表名</param>
        /// <returns></returns>
        public static int GetMaxID(string FieldName, string TableName)
        {
            string SQLString = "select isnull(max(" + FieldName + ")+1,1) from " + TableName;
            object obj = ExecuteScalar(SQLString);
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return int.Parse(obj.ToString());
            }
        }

        /// <summary>
        /// 判断是否存在
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns></returns>
        public static bool Exists(string SQLString)
        {
            object obj = ExecuteScalar(SQLString);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        /// <summary>
        /// 参数的SQL语句,判断是否存在
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <param name="cmdParms">参数内容</param>
        /// <returns></returns>
        public static bool Exists(string SQLString, params SqlParameter[] cmdParms)
        {
            object obj = ExecuteScalar(SQLString, cmdParms);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        #endregion
    }
}